import sqlite3

import pandas as pd
from flask import Flask, render_template

app = Flask(__name__)

def get_weather_data():
    conn = sqlite3.connect('E:/Procedure/Python/Assignment/Weather/city_weather.db')
    df = pd.read_sql_query("SELECT name, date, high_temp, low_temp FROM weathers", conn)
    conn.close()
    return df

@app.route('/')
def index():
    weather_data = get_weather_data()
    cities = weather_data['name'].unique()
    return render_template('index.html', cities=cities)

@app.route('/<city>')
def city_weather(city):
    weather_data = get_weather_data()
    city_data = weather_data[weather_data['name'] == city]
    if city_data.empty:
        return "City not found", 404
    return render_template('city_weather.html', city=city, city_data=city_data)

@app.route('/<city>/temperature')
def city_temperature(city):
    weather_data = get_weather_data()
    city_data = weather_data[weather_data['name'] == city]
    if city_data.empty:
        return "City not found", 404
    return render_template('city_temperature.html', city=city, city_data=city_data)

if __name__ == '__main__':
    app.run(debug=True)